package Util;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class MysqlOperation {
    public static int insertAccount(Account account) {
        Connection conn = MysqlGetConnection.getConn();
        int i = 0;
        String sql = "insert into account (name,password,regdate,runningdays,maxrunningdays) values(?,?,?,?,?)";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, account.getName());
            pstmt.setString(2, account.getPassword());
            pstmt.setLong(3, account.getRegdate());
            pstmt.setLong(4, account.getRunningdays());
            pstmt.setLong(5, account.getMaxrunningdays());
            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    public static int updateAccount(Account account) {
        Connection conn = MysqlGetConnection.getConn();
        int i = 0;
        String sql = "update account set name = ? , password = ? , runningdays = ?,maxrunningdays = ? where name= ? ";
        PreparedStatement pstmt;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, account.getName());
            pstmt.setString(2, account.getPassword());
            pstmt.setLong(3, account.getRunningdays());
            pstmt.setLong(4, account.getMaxrunningdays());
            pstmt.setString(5, account.getName());
            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }
    public static Account getAccount(String name) {
        Connection conn = MysqlGetConnection.getConn();
        String sql = "select * from account where name = ?";
        PreparedStatement pstmt;
        String password = "";
        long runningdays=0;
        long maxrunningdays=0;
        long regdate=0;
        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, name);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                password = rs.getString("password");
                regdate = rs.getLong("regdate");
                runningdays=rs.getLong("runningdays");
                maxrunningdays=rs.getLong("maxrunningdays");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        Account ans = new Account(name, password, regdate, runningdays,maxrunningdays);
        return ans;
    }
    public static int insertSignInHistory(Account account) {
        Connection conn = MysqlGetConnection.getConn();
        int i = 0;
        String sql = "insert into signinhistory (name,signindate) values(?,?)";
        PreparedStatement pstmt;
        long date = new Date().getTime();
        long lastdate=getSignInHistoryLastDate(account);
        if (hasSignInHistory(account,date))return 0;
        if (Others.isrunningDay(lastdate,date)||account.getRunningdays()<1)
        {
            account.setRunningdays(account.getRunningdays()+1);
            if(account.getRunningdays()>account.getMaxrunningdays())account.setMaxrunningdays(account.getRunningdays());
            updateAccount(account);
        }

        try {
            pstmt = (PreparedStatement) conn.prepareStatement(sql);
            pstmt.setString(1, account.getName());
            pstmt.setLong(2,date);
            i = pstmt.executeUpdate();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    public static boolean hasSignInHistory(Account account,long date) {
        int ans = 0;
        Connection conn = MysqlGetConnection.getConn();
        long mod=24*60*60*1000;
        long date1=date - (date + 8*60*60*1000) % mod;//时区东八区
        long date2=date1+mod;
        String sql = "select count(*) from signinhistory where name = ? and signindate>=? and signindate<=?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, account.getName());
            pstmt.setLong(2, date1);
            pstmt.setLong(3, date2);
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            ans = rs.getInt(1);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ans>0;
    }
    public static List<Long> getSignInHistory(Account account,int year,int mouth) throws ParseException {
        DateFormat dateFormat2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        long mindate = dateFormat2.parse(year+"-"+mouth+"-1 00:00:00").getTime();
        if(mouth>=12)
        {
            mouth=1;
            year++;
        }
        long maxdate = dateFormat2.parse(year+"-"+mouth+"-1 00:00:00").getTime();
        Connection conn = MysqlGetConnection.getConn();
        String sql = "select * from signinhistory where name = ? and signindate>=? and signindate<=? order by signindate asc ";
        List<Long> ans = new ArrayList<>();
        PreparedStatement pstmt;
        long signindate = 0;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, account.getName());
            pstmt.setLong(2, mindate);
            pstmt.setLong(3, maxdate);
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                signindate = rs.getLong("signindate");
                ans.add(signindate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ans;
    }
    public static List<Long> getAllSignInHistory(Account account) {
        Connection conn = MysqlGetConnection.getConn();
        String sql = "select * from signinhistory where name = ? order by signindate asc ";
        List<Long> ans = new ArrayList<>();
        PreparedStatement pstmt;
        long signindate = 0;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, account.getName());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                signindate = rs.getLong("signindate");
                ans.add(signindate);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ans;
    }
    public static int getSignInHistoryNum(Account account) {
        int ans = 0;
        Connection conn = MysqlGetConnection.getConn();
        String sql = "select count(*) from signinhistory where name = ?";
        PreparedStatement pstmt;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, account.getName());
            ResultSet rs = pstmt.executeQuery();
            rs.next();
            ans = rs.getInt(1);

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ans;
    }
    public static long getSignInHistoryLastDate(Account account) {
        Connection conn = MysqlGetConnection.getConn();
        String sql = "select * from signinhistory where name = ?  order by signindate desc limit 1";
        PreparedStatement pstmt;
        long signindate = 0;
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, account.getName());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                signindate = rs.getLong("signindate");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return signindate;
    }
}